package fpt.edu.vn.dao;

import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.classic.Session;
import org.hsqldb.lib.StringUtil;

import fpt.edu.vn.model.Districts;
import fpt.edu.vn.model.Locations;
import fpt.edu.vn.model.Provinces;
import fpt.edu.vn.model.Topics;
import fpt.edu.vn.util.HibernateUtil;

/* 
 * LocationDao.java
 *  
 * 0.1
 * 
 * 10/15/2012 
 *  
 * Copyright notice 
 *  
 * Modification Logs: 
 *   DATE        AUTHOR      DESCRIPTION 
 *  -------------------------------------------------------- 
 *  15-Oct-2012  ThuongND    Create new 
 */
public class LocationDao {

	public Locations add(Locations location) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		session.beginTransaction();
		session.save(location);
		session.getTransaction().commit();
		return location;
	}
	public Provinces add(Provinces province) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		session.beginTransaction();
		session.save(province);
		session.getTransaction().commit();
		return province;
	}
	public Districts add(Districts district) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		session.beginTransaction();
		session.save(district);
		session.getTransaction().commit();
		return district;
	}
	// location exist
	@SuppressWarnings("unchecked")
	public List<Locations> locationexist(String locationName, String longitude,
			String latitude, String description) {

		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		session.beginTransaction();
		List<Locations> locations = null;
		try {
			StringBuffer hsql = new StringBuffer();
			hsql.append("FROM Locations ");
			hsql.append("WHERE 1 = 1 ");
			if(!StringUtil.isEmpty(locationName))
			hsql.append("AND LOWER(locationName) LIKE :locationName ");
			if(!StringUtil.isEmpty(description))
			hsql.append("AND LOWER(description) LIKE :description ");
			if(!StringUtil.isEmpty(latitude))
			hsql.append("AND LOWER(latitude) LIKE :latitude ");
			if(!StringUtil.isEmpty(longitude))
			hsql.append("AND LOWER(longitude) LIKE :longitude ");

			Query query = session.createQuery(hsql.toString());
			if(!StringUtil.isEmpty(locationName))
			query.setParameter("locationName", "%" + locationName+ "%");
			if(!StringUtil.isEmpty(description))
			query.setParameter("description","%" +  description+ "%");
			if(!StringUtil.isEmpty(latitude))
			query.setParameter("latitude", "%" + latitude+ "%");
			if(!StringUtil.isEmpty(longitude))
			query.setParameter("longitude","%" +  longitude+ "%");

			locations = query.list();

		} catch (HibernateException e) {
			e.printStackTrace();
		}
		return locations;
	}
	
	//province check
		@SuppressWarnings("unchecked")
		public List<Provinces> provinceexist(String provinceName, String longitude,
				String latitude) {

			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			List<Provinces> provinces = null;
			try {
				StringBuffer hsql = new StringBuffer();
				hsql.append("FROM Provinces ");
				hsql.append("WHERE 1 = 1 ");
				if(!StringUtil.isEmpty(provinceName))
				hsql.append("AND LOWER(provinceName) LIKE :provinceName ");
				if(!StringUtil.isEmpty(latitude))
				hsql.append("AND LOWER(latitude) LIKE :latitude ");
				if(!StringUtil.isEmpty(longitude))
				hsql.append("AND LOWER(longitude) LIKE :longitude ");

				Query query = session.createQuery(hsql.toString());
				if(!StringUtil.isEmpty(provinceName))
				query.setParameter("provinceName", "%" + provinceName+ "%");
				if(!StringUtil.isEmpty(latitude))
				query.setParameter("latitude", "%" + latitude+ "%");
				if(!StringUtil.isEmpty(longitude))
				query.setParameter("longitude","%" +  longitude+ "%");

				provinces = query.list();

			} catch (HibernateException e) {
				e.printStackTrace();
			}
			return provinces;
		}
		
		@SuppressWarnings("unchecked")
		public List<Districts> districtexist(String districtName, String longitude,
				String latitude) {

			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			List<Districts> district = null;
			try {
				StringBuffer hsql = new StringBuffer();
				hsql.append("FROM Districts ");
				hsql.append("WHERE 1 = 1 ");
				if(!StringUtil.isEmpty(districtName))
				hsql.append("AND LOWER(districtName) LIKE :districtName ");
				if(!StringUtil.isEmpty(latitude))
				hsql.append("AND LOWER(latitude) LIKE :latitude ");
				if(!StringUtil.isEmpty(longitude))
				hsql.append("AND LOWER(longitude) LIKE :longitude ");

				Query query = session.createQuery(hsql.toString());
				if(!StringUtil.isEmpty(districtName))
				query.setParameter("districtName", "%" + districtName+ "%");
				if(!StringUtil.isEmpty(latitude))
				query.setParameter("latitude", "%" + latitude+ "%");
				if(!StringUtil.isEmpty(longitude))
				query.setParameter("longitude","%" +  longitude+ "%");

				district = query.list();

			} catch (HibernateException e) {
				e.printStackTrace();
			}
			return district;
		}
	//---check

	//list location
		
		@SuppressWarnings("unchecked")
		public List<Locations> lstlocationload(int districtId,int locationTypeId) {

			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			List<Locations> locations = null;
			try {
				StringBuffer hsql = new StringBuffer();
				hsql.append("FROM Locations ");
				hsql.append("WHERE 1 = 1 ");
				if(districtId!=0)
				hsql.append("AND LOWER(districtId) = :districtId ");
				hsql.append("AND LOWER(locationTypeId) = :locationTypeId ");
				

				Query query = session.createQuery(hsql.toString());
				if(districtId!=0)
				query.setParameter("districtId", districtId);
				query.setParameter("locationTypeId",locationTypeId);
				
				locations = query.list();

			} catch (HibernateException e) {
				e.printStackTrace();
			}
			return locations;
		}
		@SuppressWarnings("unchecked")
		public List<Locations> lstlocationdelete(int locationId) {

			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			List<Locations> locations = null;
			try {
				StringBuffer hsql = new StringBuffer();
				hsql.append("FROM Locations ");
				hsql.append("WHERE 1 = 1 ");
				
				hsql.append("AND LOWER(locationId) = :locationId ");
				

				Query query = session.createQuery(hsql.toString());
				query.setParameter("locationId", locationId);
				locations = query.list();

			} catch (HibernateException e) {
				e.printStackTrace();
			}
			return locations;
		}
		
		//delete location
		// delete topic
		public boolean deletedLocation(int locationId) {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			boolean delete = false;
			try {
				String hql = "delete from Locations where locationId =" + locationId;
				Query query = session.createQuery(hql);
				int rowCount = query.executeUpdate();
				if (rowCount < 1) {
				} else {
					delete = true;
				}

			} catch (HibernateException e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			session.getTransaction().commit();
			return delete;
		}
}
